﻿using HDS.Data;
using HDS.IService.Master;
using HDS.Model;
using HDS.Model.Entity.Master;
using HDS.Model.Entity.View;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace HDS.Service.Master
{
    public class MappingService : IMappingService
    {
        private MasterDbContext dbContext = new MasterDbContext();
        private LogDbContext logDbContext = new LogDbContext();

        public async Task<DataPagingModel<VMappingHotelOpt>> GetVMappingHotels(int pageIndex, int pageSize, Expression<Func<VMappingHotelOpt, bool>> whereExpression, string whereSql)
        {
            var sql = "SELECT";
            sql += " sh.id,";
            sql += " sh.supplier,";
            sql += " sh.supplier_hotel_id,";
            sql += " sh.supplier_hotel_id1,";
            sql += " sh.hotel_name_en supplier_hotel_name_en,";
            sql += " sh.hotel_name_cn supplier_hotel_name_cn,";
            sql += " sh.hotel_star supplier_hotel_star,";
            sql += " sh.country_code supplier_country_code,";
            sql += " sh.country_name supplier_country_name,";
            sql += " sh.province_code supplier_province_code,";
            sql += " sh.province_name supplier_province_name,";
            sql += " sh.city_code supplier_city_code,";
            sql += " sh.city_name supplier_city_name,";
            sql += " sh.zone_code supplier_zone_code,";
            sql += " sh.zone_name supplier_zone_name,";
            sql += " sh.postalcode supplier_postalcode,";
            sql += " sh.hotel_address_en supplier_hotel_address_en,";
            sql += " sh.hotel_address_cn supplier_hotel_address_cn,";
            sql += " sh.hotel_longitude supplier_hotel_longitude,";
            sql += " sh.hotel_latitude supplier_hotel_latitude,";
            sql += " sh.hotel_tel supplier_hotel_tel,";
            sql += " sh.hotel_fax supplier_hotel_fax,";
            sql += " sh.hotel_email supplier_hotel_email,";
            sql += " sh.hotel_web supplier_hotel_web,";
            sql += " shm.supplier_top,";
            sql += " shm.supplier_sensitive,";
            sql += " shm.is_avail,";
            sql += " m.hotel_id,";
            sql += " m.mapping_status,";
            sql += " m.updated_by,";
            sql += " m.updated_by_name,";
            sql += " m.updated_time,";
            sql += " h.hotel_name_en,";
            sql += " h.hotel_name_cn,";
            sql += " h.hotel_star,";
            sql += " h.country_code,";
            sql += " h.country_name,";
            sql += " h.province_code,";
            sql += " h.province_name,";
            sql += " h.city_code,";
            sql += " h.city_name,";
            sql += " h.zone_code,";
            sql += " h.zone_name,";
            sql += " h.postalcode,";
            sql += " h.hotel_address_en,";
            sql += " h.hotel_address_cn,";
            sql += " h.hotel_longitude,";
            sql += " h.hotel_latitude,";
            sql += " h.hotel_tel,";
            sql += " h.hotel_fax,";
            sql += " h.hotel_email,";
            sql += " h.hotel_web,";
            sql += " h.hotel_status";
            sql += " FROM supplier_hotel sh";
            sql += " LEFT JOIN supplier_hotel_mark shm ON sh.supplier = shm.supplier AND sh.supplier_hotel_id = shm.supplier_hotel_id";
            sql += " LEFT JOIN mapping_hotel m ON m.supplier = sh.supplier AND m.supplier_hotel_id = sh.supplier_hotel_id";
            sql += " LEFT JOIN hotel h ON h.hotel_id = m.hotel_id";

            RefAsync<int> total = 0;
            var rows = await dbContext.Db.SqlQueryable<VMappingHotelOpt>(sql)
                .WhereIF(whereExpression != null, whereExpression)
                .WhereIF(!string.IsNullOrEmpty(whereSql), whereSql)
                .OrderBy(e => e.Id, OrderByType.Desc)
                .ToPageListAsync(pageIndex, pageSize, total);

            return new DataPagingModel<VMappingHotelOpt>
            {
                PageIndex = pageIndex,
                PageSize = pageSize,
                PageTotal = (total.Value + pageSize - 1) / pageSize,
                Total = total.Value,
                Rows = rows
            };
        }

        public async Task<List<VMappingHotel>> GetVMappingHotels(int hotelId)
        {
            return await dbContext.Db.Queryable<MappingHotel, SupplierHotel, SupplierHotelMark>((mh, sh, shm) => new JoinQueryInfos(
                     JoinType.Left, mh.Supplier.Equals(sh.Supplier) && mh.SupplierHotelId.Equals(sh.SupplierHotelId) && mh.MappingStatus,
                     JoinType.Left, mh.Supplier.Equals(shm.Supplier) && mh.SupplierHotelId.Equals(shm.SupplierHotelId)
                ))
                .Where(mh => mh.MappingStatus && mh.HotelId.Equals(hotelId))
                .OrderBy(mh => mh.Id, OrderByType.Desc)
                .Select<VMappingHotel>().ToListAsync();
        }

        public async Task<bool> SaveMappingHotel(MappingHotel entity)
        {
            return await dbContext.MappingHotel.InsertAsync(entity);
        }

        public async Task<bool> DeleteMappingHotel(int hotelId, string supplier, string supplierHotelId)
        {
            await dbContext.MappingRoom.DeleteAsync(e => e.HotelId.Equals(hotelId) && e.Supplier.Equals(supplier) && e.SupplierHotelId.Equals(supplierHotelId));
            return await dbContext.MappingHotel.DeleteAsync(e => e.HotelId.Equals(hotelId) && e.Supplier.Equals(supplier) && e.SupplierHotelId.Equals(supplierHotelId));
        }
        
        public async Task<List<VMappingRoom>> GetVMappingRooms(int hotelId)
        {
            var sql = " SELECT";
            sql += " r.`room_id`,";
            sql += " r.`hotel_id`,";
            sql += " r.`room_name_en`,";
            sql += " r.`room_name_cn`,";
            sql += " r.`room_area`,";
            sql += " m.`supplier`,";
            sql += " m.`supplier_hotel_id`,";
            sql += " m.`supplier_room_id`,";
            sql += " m.`mapping_status`,";
            sql += " m.`updated_by`,";
            sql += " m.`updated_by_name`,";
            sql += " m.`updated_time`,";
            sql += " sr.`room_name_en` supplier_room_name_en,";
            sql += " sr.`room_name_cn` supplier_room_name_cn,";
            sql += " sr.`room_desc`";
            sql += " FROM `hotel_room` r";
            sql += " LEFT JOIN mapping_room m ON m.`hotel_id` = r.`hotel_id` AND m.`room_id` = r.`room_id`";
            sql += " LEFT JOIN supplier_room sr ON sr.`supplier` = m.`supplier` AND sr.`supplier_hotel_id` = m.`supplier_hotel_id` AND sr.`supplier_room_id` = m.`supplier_room_id`";
            sql += $" WHERE r.`room_status` = 1 AND r.hotel_id={hotelId}";
            return await dbContext.Db.SqlQueryable<VMappingRoom>(sql).ToListAsync();
        }

        public async Task<List<SupplierRoom>> GetUnMappingRooms(int hotelId)
        {
            var sql = " SELECT r.*";
            sql += " FROM `supplier_room` r";
            sql += " INNER JOIN `mapping_hotel` m ON r.`supplier` = m.supplier AND r.`supplier_hotel_id` = m.supplier_hotel_id AND m.`mapping_status` = 1";
            sql += " LEFT JOIN `mapping_room` mr ON mr.`hotel_id` = m.`hotel_id` AND mr.`supplier` = r.`supplier` AND mr.`supplier_hotel_id` = r.`supplier_hotel_id` AND mr.`supplier_room_id` = r.`supplier_room_id`";
            sql += $" WHERE mr.`id` IS NULL AND m.`hotel_id` = {hotelId}";

            return await dbContext.Db.SqlQueryable<SupplierRoom>(sql).ToListAsync();
        }

        public async Task<int> GetUnMappingRoomCount(int hotelId)
        {
            var sql = " SELECT COUNT(r.id)";
            sql += " FROM `supplier_room` r";
            sql += " INNER JOIN `mapping_hotel` m ON r.`supplier` = m.supplier AND r.`supplier_hotel_id` = m.supplier_hotel_id AND m.`mapping_status` = 1";
            sql += " LEFT JOIN `mapping_room` mr ON mr.`hotel_id` = m.`hotel_id` AND mr.`supplier` = r.`supplier` AND mr.`supplier_hotel_id` = r.`supplier_hotel_id` AND mr.`supplier_room_id` = r.`supplier_room_id`";
            sql += $" WHERE mr.`id` IS NULL AND m.`hotel_id` = {hotelId}";

            return await dbContext.Db.Ado.GetIntAsync(sql);
        }

        public async Task<bool> SaveRoomMappings(List<MappingRoom> mappingRoomList)
        {
            return await dbContext.MappingRoom.InsertRangeAsync(mappingRoomList);
        }

        public async Task<bool> CancelRoomMapping(string supplier, string supplierHotelId, string supplierRoomId)
        {
            return await dbContext.MappingRoom.DeleteAsync(e => e.Supplier.Equals(supplier) && e.SupplierHotelId.Equals(supplierHotelId) && e.SupplierRoomId.Equals(supplierRoomId));
        }
    }
}
